package com.cn.sx;
import org.apache.commons.csv.CSVUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.datasource.*;
import javax.sql.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Timestamp;
import java.util.*;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.Ostermiller.util.CSVParser;

public class bean1 {
	private static final Log log=LogFactory.getLog(bean1.class);
    private DataSource dataSource;
    private DataSourceTransactionManager tran;
    private String[] arrayType;
    private String[] arrayType1;
    private String[] arrayType2;
    private String[] arrayType3;
    private String[] arrayType4;
    private int num=0;
    public String temp="";
    
	public String getTemp() {
		return temp;
	}
	public void setTemp(String temp) {
		this.temp = temp;
	}
	public String[] getArrayType3() {
		return arrayType3;
	}
	public void setArrayType3(String[] arrayType3) {
		this.arrayType3 = arrayType3;
	}
	public String[] getArrayType4() {
		return arrayType4;
	}
	public void setArrayType4(String[] arrayType4) {
		this.arrayType4 = arrayType4;
	}
	public String[] getArrayType2() {
		return arrayType2;
	}
	public void setArrayType2(String[] arrayType2) {
		this.arrayType2 = arrayType2;
	}
	public String[] getArrayType1() {
		return arrayType1;
	}
	public void setArrayType1(String[] arrayType1) {
		this.arrayType1 = arrayType1;
	}
	public String[] getArrayType() {
		return arrayType;
	}
	public void setArrayType(String[] arrayType) {
		this.arrayType = arrayType;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public DataSourceTransactionManager getTran() {
		return tran;
	}
	public void setTran(DataSourceTransactionManager tran) {
		this.tran = tran;
	}
	
	public String gettemp(String[] aa){
    	   if(aa.length>3){
    		   return getChar(aa[2]);
    	   }else{
    		   return "fail"; 
    	   }

	}
	public static String getERRO(Exception e){
		String str="";
		try{
            StringWriter sw = new StringWriter();  
            e.printStackTrace(new PrintWriter(sw, true));  
            str = sw.toString(); 	
		}catch(Exception e1){
			str=e.toString();
			log.error("getERRO:"+e1.toString());
		}
		return str;
	}
	public int getCustomerNum(String a){
		String sql="select count(*) from customer where stringfield1='custlist_"+a+"'";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		log.info("sql:"+sql);
		int count=jdbcTemplate.queryForInt(sql);
		return count;
	}
	public int getCardNum(String a){
		String sql="select count(*) from customer_card where caseid in (select caseid from customer where stringfield1='custlist_"+a+"')";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		log.info("sql:"+sql);
		int count=jdbcTemplate.queryForInt(sql);
		return count;
	}	
	public int getcount1(String a){
		String sql="select count(*) from custlists where custlist_xxx='"+a+"'";
		log.info("sql:"+sql);
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		int count=jdbcTemplate.queryForInt(sql);
		return count;
	}
	public void execute1(){
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.execute("{call dbo.insertcustomer()}");
	}
    public int[] update(String[] sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int[] resultList=resultList = jdbcTemplate.batchUpdate(sql);
        return resultList;
  }
//    public int testupdate(String sql,String a,String b){
//    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
//    	int f=jdbcTemplate.update(sql,a,b);
//    	SimpleJdbcTemplate simplejdbctemp
//    }
    public int update1(String sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int resultList = jdbcTemplate.update(sql);
        return resultList;
  }
    public int update123(String a,String b,String c) throws Exception {
    	if(b.length()>100){
    		b=b.substring(0,100);
    	}
    	String sql="insert into failrecord (time1,operate,description1,description2)Values(getdate(),'"+a+"','"+b+"','"+c+"')";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        log.info("sql:"+sql);
        int resultList = jdbcTemplate.update(sql);
        return resultList;
  }
	public String getindexchar(){
		if(arrayType==null){
			log.info("获得indexchar失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得indexchar失败");
				return "false";
			}else{
				if(arrayType[2].equals("")){
					log.info("获得indexchar失败");
					return "false";
				}else{
//					log.info("获得indexchar成功:"+arrayType[2]);
					return arrayType[2];
				}
			}
		}
	}
	
	public String getdatebaseType(){
		if(arrayType==null){
			log.info("获得datebaseType失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得datebaseType失败");
				return "false";
			}else{
				if(arrayType[0].equals("")||arrayType[0].equals("null")){
					log.info("获得datebaseType失败");
					return "false";
				}else{
//					log.info("获得datebaseType成功:"+arrayType[0]);
					return arrayType[0];
				}
			}
		}
	}
	public String gettableName(){
		if(arrayType==null){
			log.info("获得tableName失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得tableName失败");
				return "false";
			}else{
				if(arrayType[1].length()<1){
					log.info("获得tableName失败");
					return "false";
				}else{
//					log.info("获得tableName成功:"+arrayType[1]);
					return arrayType[1];
				}
			}
		}
	}
	
	public String gettableName1(){
		if(arrayType==null){
			log.info("获得tableName失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得tableName失败");
				return "false";
			}else{
				if(arrayType[3].length()<1){
					log.info("获得tableName失败");
					return "false";
				}else{
//					log.info("获得tableName成功:"+arrayType[3]);
					return arrayType[3];
				}
			}
		}
	}
	
	public String getChar(String a){
		int c=a.indexOf("\"");
		if(c==-1){
			return a;
		}else{
			a=a.replace("\"", "");
			return a;
		}
	}
	public String getChar1(String a){
		a=a.replace("\"", "");
		a=a.replace("'", "");
		return a;
	}
	public boolean judgeTable(String[] arrayType11,String[] arrayType22){
		if(arrayType11!=null&&arrayType11.length>0){
			if(arrayType22!=null&&arrayType22.length>0){
				if(arrayType11.length==arrayType22.length){
					return true;
				}else{
					log.info("获得字段类型失败");
					return false;
				}
			}else{
				log.info("获得字段类型失败");
				return false;
			}
		}else{
			log.info("获得字段失败");
			return false;
		}
	}
	
	public String getsql_1(String tableName,String datebaseType,String[] arrayType11){
		String sql="";
		if(datebaseType.equals("MSSQL")){
			sql="insert into "+  tableName  +"(";
			for(int i=0;i<arrayType11.length;i++){
				if(i==(arrayType11.length-1)){
					sql=sql+arrayType11[i]+")";
				}else{
					sql=sql+arrayType11[i]+",";
				}
			}
			return sql;
		}else{
			log.info("获取tableName失败:"+tableName);
			return sql;
		}
	   
	}
	
	public static String bSubstring(String s, int length) throws Exception{
		
		byte[] bytes = s.getBytes("Unicode");
        int n = 0; // 表示当前的字节数
        int i = 2; // 要截取的字节数，从第3个字节开始
        for (; i < bytes.length && n < length; i++)
        {
            // 奇数位置，如3、5、7等，为UCS2编码中两个字节的第二个字节
            if (i % 2 == 1)
            {
                n++; // 在UCS2第二个字节时n加1
            }
            else
            {
                // 当UCS2编码的第一个字节不等于0时，该UCS2字符为汉字，一个汉字算两个字节
                if (bytes[i] != 0)
                {
                    n++;
                }
            }
        }
        // 如果i为奇数时，处理成偶数
        if (i % 2 == 1)

        {
            // 该UCS2字符是汉字时，去掉这个截一半的汉字
            if (bytes[i - 1] != 0)
                i = i - 1;
            // 该UCS2字符是字母或数字，则保留该字符
            else
                i = i + 1;
        }

        return new String(bytes, 0, i, "Unicode");
	}
	public String getsql_2(String[] data1,String[] arrayType22,String tablename,String index,String tablename1,String temp_sql){
		String datebaseType=getdatebaseType();
		String sql="(";
		int judge=0;
		
		if(data1!=null&&data1.length>0){
			if(data1.length>2){
				if(tablename.equals(tablename1)){
					if(!index.equals(data1[2])){
						try {
							update123("数据导入","批次信息于文件名不一致",data1[2].toString());
						} catch (Exception e) {
							log.info(getERRO(e));
						}
						return "false";
					}
				}
			}
			for(int i=0;i<data1.length;i++){
				data1[i]=data1[i].toString().replace("'","''");
				String bijiao=arrayType22[i];
                try{
                	if(arrayType22[i].toString().indexOf("-")!=-1){
                		String[] temp=arrayType22[i].split("-");
                		if(temp!=null&&temp.length>1){
                			bijiao=temp[0];
                			int judgeLength=Integer.parseInt(temp[1]);
                			if(data1[i].length()>judgeLength){
                				try{
                					
                					data1[i]=bSubstring(data1[i],judgeLength-1);
                					
                				}catch(Exception e){
                					log.info(getERRO(e));
                					update123("数据导入","字符串截取错误,按一半截取",data1[i].replace("'",""));
                					data1[i]=data1[i].substring(0,judgeLength/2);
                				}
                				judge=1;
                			}
                		}
                	}
                }catch(Exception e){
                	log.info(getERRO(e));
                }
				try{
					if(bijiao.equals("string")){
						data1[i]="'"+data1[i]+"'";
					}else if(bijiao.equals("int")){
						
					}else if(bijiao.equals("date")){
						if(datebaseType.equals("MSSQL")){
							data1[i]="'"+data1[i]+"'";
						}else{
							log.info("获取datebaseType失败:"+datebaseType);
							return "false";
						}
						
					}else{
						log.info("获得非法字段类型");
						return "false";
					}
					
					if(i==(data1.length-1)){
						sql=sql+data1[i]+")";
					}else{
						sql=sql+data1[i]+",";
					}								
				}catch(Exception e){
					log.info(getERRO(e));
					data1[i]="'"+data1[i]+"'";
				}
				
			}
			if(judge==1){
				try {
					update123("数据导入","字段过长",(temp_sql+sql).replace("'",""));
				} catch (Exception e) {
					log.info(getERRO(e));
				}
			}
			return temp_sql+sql;
		}else{
			log.info("获得TXT数据失败");
			return "false";
		}
		
	}
	
	public String getsql_2_1(String[] data1,String[] arrayType22){
		String datebaseType=getdatebaseType();
		String sql="(";
		if(data1!=null&&data1.length>0){
			for(int i=0;i<data1.length;i++){
				try{
					data1[i]=getChar(data1[i]);
					data1[i]="'"+data1[i]+"'";			
					if(i==(data1.length-1)){
						sql=sql+data1[i]+")";
					}else{
						sql=sql+data1[i]+",";
					}
					
				}catch(Exception e){
					log.info(getERRO(e));
					data1[i]="'"+data1[i]+"'";
				}
				
			}
			return sql;
		}else{
			log.info("获得TXT数据失败");
			return "false";
		}
		
	}
	
	public String getfilepath(String path,String filename,String index_flag_date){
		String indexchar=getindexchar();
		path=path.substring(0, path.lastIndexOf("\\")+1);
		File file = new File(path);
		if(file==null){
			log.info("没有文件");
			return "nofile";
		}else{
			if(file.listFiles().length>0){
				for (int i = 0; i < file.listFiles().length; i++){
					if(!filename.equals(file.listFiles()[i].getName())){
						String a=file.listFiles()[i].getName();
						if(file.listFiles()[i].getName().indexOf(indexchar)!=-1&&a.indexOf(index_flag_date)!=-1){
							
								return file.listFiles()[i].getPath();
	
						}
					}
					
				}
			}else{
				log.info("没有新文件");
				return "nofile";
			}
		}

		log.info("没有新文件");
		return "nofile";
		
	}
	
	public String[] getupdatesql(InputStreamReader input,String sql1,String[] arrayType22,String tablename,String index,String tablename1) throws Exception{
		String[] updatesql=null;
		String[] result=null;
		BufferedReader reader = new BufferedReader(input);
        List<String> cols = new ArrayList<String>();
        int rows = 0;
        int n=0;
        for(String line = reader.readLine(); line != null; line = reader.readLine()){
        	String sql="";
        	try{
        		
        		result=CSVUtils.parseLine(line);
        		sql=sql1+"  Values  ";
        		String tempsql=getsql_2(result,arrayType22,tablename,index,tablename1,sql);
        		if(tempsql.equals("false")){
        			sql="false";
        		}else{
        			sql=tempsql;
        		}
        	}catch(Exception e){
        		    log.info(getERRO(e));
					update123("数据导入",e.toString(),getChar1(line));
        	}
        	if(sql.equals("false")){
        		cols.add(rows,"");	
    			rows ++;
        	}else{
        		cols.add(rows,sql);	
    			rows ++;	
        	}
        }
        updatesql=new String[rows];
        for(int i=0;i<cols.size();i++){
        	updatesql[i]=cols.get(i);
        }
        return updatesql;
	}
	
	public String[] getupdatesql_1(InputStreamReader input,String sql1,String[] arrayType22) throws Exception{
		String[] updatesql;
		String[] result;
		BufferedReader reader = new BufferedReader(input);
        List<String> cols = new ArrayList<String>();
        int rows = 0;
        int n=0;
        for(String line = reader.readLine(); line != null; line = reader.readLine()){
        	String sql="";
        	try{
            	result=CSVUtils.parseLine(line);
            	sql=sql1+"  Values  "+getsql_2_1(result,arrayType22); 
        	}catch(Exception e){
        		log.info(getERRO(e));
        		update123("数据导入",e.toString(),getChar1(line));
        	}
			cols.add(rows, sql);
			rows ++;
        }
        updatesql=new String[rows];
        for(int i=0;i<cols.size();i++){
        	updatesql[i]=cols.get(i);
        }
        return updatesql;
	}
	
	public int getcount(int[] result1){
    	int count=0;
        for(int i=0;i<result1.length;i++){
        	count=count+result1[i];
        }
        return count;
	}
	
	public String getsql1(String[] arrayType11,String[] arrayType22,String tableName,String datebaseType){
		String sql1="";
    	if(judgeTable(arrayType11,arrayType22)){
    		sql1=getsql_1(tableName,datebaseType,arrayType11);
    		if(sql1.equals("")||sql1.length()<1){
        		return "fail";
    		}else{
    			return sql1;
    		}
    	}else{
    		return "fail";
    	}
	}
	
}
